/*
 * @Author: xzy 1453077527@qq.com
 * @Date: 2025-08-25 14:05:13
 * @LastEditors: xzy 1453077527@qq.com
 * @LastEditTime: 2025-08-26 17:11:33
 * @Description:
 */
package com.xtedu.examproject.dao.impl;

import com.xtedu.examproject.dao.base.RoleDetailInfoDao;
import com.xtedu.examproject.dao.base.TeacherDao;
import com.xtedu.examproject.entity.Course;
import com.xtedu.examproject.entity.Teacher;
import com.xtedu.examproject.util.DBUtil;
import com.xtedu.examproject.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TeacherDaoImpl implements TeacherDao, RoleDetailInfoDao {
    @Override
    public int insert(Teacher teacher) throws Exception {
        String sql = "insert into teachers (teacher_name,phone,teacher_type,state) values (?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, teacher.getName());
            ps.setString(2, teacher.getPhone());
            ps.setString(3, teacher.getType());
            ps.setBoolean(4, teacher.isState());
            return ps.executeUpdate();
        }
    }

    @Override
    public int update(Teacher teacher) throws Exception {
        String sql = "update teachers set teacher_name=?,phone=?,teacher_type=?,state=? where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, teacher.getName());
            ps.setString(2, teacher.getPhone());
            ps.setString(3, teacher.getType());
            ps.setBoolean(4, teacher.isState());
            return ps.executeUpdate();
        }
    }

    @Override
    public int delete(Teacher teacher) throws Exception {
        String sql = "delete from teachers where id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, teacher.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int deleteById(int id) throws Exception {
        return 0;
    }

    @Override
    public Teacher findById(int id) throws Exception {
        String sql = "select * from teachers where id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            Teacher teacher = new Teacher();
            while (rs.next()) {
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setState(rs.getBoolean("state"));
            }
            return teacher;
        }
    }

    @Override
    public List<Teacher> findAll() throws Exception {
        String sql = "select * from teachers";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setState(rs.getBoolean("state"));
                teachers.add(teacher);
            }
            return teachers;
        }
    }

    @Override
    public PageHelper findAllByPage(int pageIndex, int pageSize) throws Exception {
        String sql = "SELECT t.*, r.role_name as type_name FROM teachers t LEFT JOIN roles r ON t.teacher_type = r.id LIMIT ?, ?";
        String countSql = "SELECT COUNT(*) FROM teachers";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql);
             PreparedStatement countPs = con.prepareStatement(countSql)) {

            // 添加分页参数
            ps.setInt(1, (pageIndex - 1) * pageSize);
            ps.setInt(2, pageSize);

            ResultSet rs = ps.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setType(rs.getString("teacher_type")); // 保持原有的type字段（存储角色ID）
                teacher.setTypeName(rs.getString("type_name")); // 新增的角色名称字段
                teacher.setState(rs.getBoolean("state"));
                teachers.add(teacher);
            }
            // 查询总数
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }
            int totalPages = (int) Math.ceil((double) totalCount / pageSize);
            return new PageHelper<>(teachers, pageIndex, pageSize, totalCount, "/teamanage/", totalPages);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }



    @Override
    public PageHelper findPageByRoleIds(int pageindex, int pagesize, int... roleIds) throws Exception {
        return null;
    }

    /**
     * 显示用户详细信息
     *
     * @param userId 用户id
     * @return 用户详细信息
     */
    @Override
    public Teacher showDetailInfo(int userId) throws Exception {
        String sql = "select t.* from (select * from users u where role_id = 1) u left join teachers t on u.info_id = t.id where u.id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            // 填入参数
            ps.setInt(1, userId);

            ResultSet rs = ps.executeQuery();
            Teacher teacher = new Teacher();
            while (rs.next()) {
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setState(rs.getBoolean("state"));
            }
            return teacher;
        }
    }

    @Override
    public List showByOwnClazz(int userId) throws Exception {
        return List.of();
    }

    @Override
    public PageHelper showPageByOwnClazz(int userId, int pageIndex, int pageSize) throws Exception {
        return null;
    }

    /**
     * 获取所有非用户老师
     *
     * @return 老师集合
     */
    @Override
    public List<Teacher> findNonUserTeacher() throws Exception {
        String sql = "select t.* from teachers t where not exists (select * from users u where u.role_id = 1 and u.info_id = t.id)";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setState(rs.getBoolean("state"));
                teachers.add(teacher);
            }
            return teachers;
        }
    }

    /**
     * 根据id删除老师
     *
     * @param teacherId
     * @return
     * @throws Exception
     */
    public boolean deleteTeacherId(int teacherId) {
        String sql = "DELETE FROM teachers WHERE id = ?";

        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql)
        ) {
            ps.setInt(1, teacherId);
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据id查询老师（分页）
     *
     * @param teacherId
     * @param pageIndex
     * @param pageSize
     * @return
     */
    public PageHelper findPageIdByTeacher(int teacherId, int pageIndex, int pageSize) throws SQLException {
        String sql = "SELECT * FROM teachers where id = ?";
        String countSql = "SELECT COUNT(*) FROM teachers where id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             PreparedStatement countPs = conn.prepareStatement(countSql)
        ) {
            ps.setInt(1, teacherId);
            countPs.setInt(1, teacherId);
            ResultSet rs = ps.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setState(rs.getBoolean("state"));
                teachers.add(teacher);
            }
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }
            int totalPages = (int) Math.ceil((double) totalCount / pageSize);
            return new PageHelper<>(teachers, pageIndex, pageSize, totalCount, "/teamanage/", totalPages);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据老师名称查询老师(分页)
     *
     * @param teacherName
     * @return
     * @throws SQLException
     */
    public PageHelper findPageNameByTeacher(String teacherName, int pageIndex, int pageSize) throws SQLException {
        String sql = "SELECT * FROM teachers WHERE teacher_name LIKE ? LIMIT ?, ?";
        String countSql = "SELECT COUNT(*) FROM teachers WHERE teacher_name LIKE ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             PreparedStatement countPs = conn.prepareStatement(countSql)
        ) {
            ps.setString(1, "%" + teacherName + "%");
            ps.setInt(2, (pageIndex - 1) * pageSize);
            ps.setInt(3, pageSize);
            countPs.setString(1, "%" + teacherName + "%");
            ResultSet rs = ps.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setState(rs.getBoolean("state"));
                teachers.add(teacher);
            }
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }
            int totalPages = (int) Math.ceil((double) totalCount / pageSize);
            return new PageHelper<>(teachers, pageIndex, pageSize, totalCount, "/teamanage/", totalPages);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据电话号码查询老师（分页）
     *
     * @param phone
     * @return
     * @throws SQLException
     */
    public PageHelper findPagePhoneByTeacher(String phone, int pageIndex, int pageSize) throws SQLException {
        String sql = "SELECT * FROM teachers WHERE phone LIKE ? LIMIT ?, ?";
        String countSql = "SELECT COUNT(*) FROM teachers WHERE phone LIKE ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             PreparedStatement countPs = conn.prepareStatement(countSql)
        ) {
            ps.setString(1, "%" + phone + "%");
            ps.setInt(2, (pageIndex - 1) * pageSize);
            ps.setInt(3, pageSize);
            countPs.setString(1, "%" + phone + "%");
            ResultSet rs = ps.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setState(rs.getBoolean("state"));
                teachers.add(teacher);
            }
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }
            int totalPages = (int) Math.ceil((double) totalCount / pageSize);
            return new PageHelper<>(teachers, pageIndex, pageSize, totalCount, "/teamanage/", totalPages);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据ID查到的信息来修改课程信息
     */
    public boolean updateTeacher(Teacher teacher) throws Exception {
        String sql = "UPDATE teachers SET teacher_name = ?, phone = ?,teacher_type=?,state = ? WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)
        ) {
            ps.setString(1, teacher.getName());
            ps.setString(2, teacher.getPhone());
            ps.setString(3, teacher.getType());
            ps.setBoolean(4, teacher.isState());
            ps.setInt(5, teacher.getId());
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
    @Override
    public List<Teacher> findTeacherByCourseId(int courseId) throws Exception {
        String sql = "select t.id,t.teacher_name from teachers t left join teachers_courses tc on t.id = tc.teacher_id where tc.course_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            ResultSet rs = ps.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teachers.add(teacher);
            }
            return teachers;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据老师类型查询老师(分页)
      * @param typeName
     * @param pageIndex
     * @param pageSize
     * @return
     * @throws Exception
     */
    public PageHelper findTeacherByType(String typeName, int pageIndex, int pageSize) throws SQLException {
        String sql = "SELECT t.*, r.role_name as type_name FROM teachers t LEFT JOIN roles r ON t.teacher_type = r.id WHERE r.role_name LIKE ? LIMIT ?, ?";
        String countSql = "SELECT COUNT(*) FROM teachers t LEFT JOIN roles r ON t.teacher_type = r.id WHERE r.role_name LIKE ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             PreparedStatement countPs = conn.prepareStatement(countSql)
        ) {
            ps.setString(1, "%" + typeName + "%");
            ps.setInt(2, (pageIndex - 1) * pageSize);
            ps.setInt(3, pageSize);
            countPs.setString(1, "%" + typeName + "%");
            ResultSet rs = ps.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("teacher_name"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setType(rs.getString("teacher_type"));
                teacher.setTypeName(rs.getString("type_name"));
                teacher.setState(rs.getBoolean("state"));
                teachers.add(teacher);
            }
            ResultSet countRs = countPs.executeQuery();
            int totalCount = 0;
            if (countRs.next()) {
                totalCount = countRs.getInt(1);
            }
            int totalPages = (int) Math.ceil((double) totalCount / pageSize);
            return new PageHelper<>(teachers, pageIndex, pageSize, totalCount, "/teamanage/", totalPages);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}
